How to create a powerful database with Excel using free data.

This article is how to create a powerful database with Microsoft Excel using free company and user data from a public library. We suggest that you do this as part of your overall data collection for any new business or startup. Why?

The following image is a screen shot of the database we created with Excel as well as free data from the Cambridge Massachusetts public library.

It was uploaded from a csv file to Google maps. It is a listing of all of the dental practices and auto repair shops in a specific area in Boston along with sales and employee data we screened for.  

Our Excel database is public information, but its accuracy would surprise you.

When we first started developing these databases (we have several cities) we imagined that free information from a public source would lack accuracy. It was far more accurate that we thought. Although things like sales numbers and employee counts were estimates, they were within a range that made them incredibly useful.

 We have used these as a first step in creating our sales funnels.

To read about creating a sales funnel, click here.

Here are the steps we used to create the excel database in the image above.

Step 1 is to get a library card.

We actually use 2. One is the Cambridge Library in Cambridge Massachusetts and the Buffalo and Erie County Public Library in Buffalo. Both have similar data systems but the data is national. You don’t have to be in Boston to get Boston data.

Go to their online system and look under “data” and then “business.” The database Buffalo has is called “Reference USA.” They are all similar.

Here you’ll see almost every major business in the United States broken down by region, address, sales, type… you name it. It’s here where you’ll start to build your database.

Step 2: Decide what data you want.

Our criterion is businesses in our marketplace. (Although there is also a database of individuals.) We sorted for companies with between $500,000 and $10,000,000 in sales in zip codes we chose from a zip code map of the city. (Google “zip code map”)

Step 3: Download the data onto an Excel Spreadsheet.

You do this by downloading the information in csv form and cutting and pasting it to a master spreadsheet. Our database for Buffalo has 19,000 businesses in 11 zip codes.

Step 4: Make a master copy.

In case you need to retrieve lost data,

Step 5: Sort, delete and categorize.

In our database, we sorted by several factors depending on what we were using the data for.

We sort by… (In order of importance for us.)

• Industry
• Zip Code
• Location (Street address)
• Sales
• Employee #

We typically don’t use the information for direct selling because it’s not accurate enough. Some of the business are gone and some have changed ownership. We DO use the data for prospecting though. A lot of the information is golden.

Keep in mind, we are not deleting data at this point. We are simply sorting the first 4-6 categories. These databases have dozens of categories.

Step 6: Determine what you want the information for.

Let me give you an example of a program we ran.

We were targeting Boston area physicians.

We uploaded all that were in the system. We saved a master and created a working copy.

Since we were targeting self-employed doctors and small practices, we deleted all doctors that were in hospitals and large clinics.

We then sorted by the above column names.

Next, we cut and pasted the columns in the order we would use. They were Company name, physicians name, address, zip code, office manager, telephone number and finally, sales.

Anyone who has ever dealt with doctors knows, everything goes through the office manager. Although office managers change, in medical offices, they tend to stay longer than anyone except for the doctors themselves.

Optional: You may also delete those companies that are not in your marketplace. In this case, we highlighted offices with more than $10 million in sales. These were outside the market range of the product we were selling. In many cases, you may want to omit smaller companies.

Step 7: (Optional) Upload to Google maps.

Google maps has an excel upload feature that allows you to create map overlays. We have one for Buffalo that is auto repair shops. They show up as stars on the map. When you hover over the star with your cursor, it shows you the criterion you entered. (Name, Address, Sales.)

Step 8: (Optional) Upload to Access.

Depending on what you use the information for, you may want to upload to Microsoft’s database program.

We do this because we use databases to add more specific information in tabular form. Depending on what you are using the data for, you may or may not want to take the time to do this.

What can you use the information for?

We have used it for several things.

• It tells us how big the marketplace for a specific industry type is. For example, you can add up the sales of all the companies in your interest group and find out how big the market is.
• You can use it to determine where the concentration of businesses is. This may help in choosing a new location.
• You can use it to create a drive-by map to determine where your competitors are and how they run their businesses.
• You can use it to determine who the leading businesses and professionals in a field and target them.
• It tells you (very roughly) what the average business of a particular business is. It’s unlikely sales numbers are accurate but the averages probably are.

We do not create databases or sitemaps for individuals because we do not have non-business clients, but this can really cut your prospecting time.

For example, you can sort for household income and use this to determine (as an example) where to focus your Footprinting. (See that articles on Footprinting in Marketing.)

You can incorporate it into your sitemap for your website and give potential customers “location data.” For example, a category “Westchester” under “Pages.”

You can use it to target neighborhoods using Google Ad targeting.  

You can also use it to target local ads like village newspapers, The Swap Sheet, message boards and event targeting.

Note: We have stayed away from making this a tutorial on Excel and Access. These programs all have tutorials. As to the database you use, it is up to you. You can buy a database program and eliminate all these steps.

One person we totally LOVE though from the aspect of excel tutorials is Sharon Smith who runs an employee search company. She is a great resource to subscribe to.

We especially love it from the prospect of creating a local footprint.

If your business has a location, you will want to know your neighbors. Using an Excel database with public library data gives you great insights as to who is out there and where they are. You can combine with public traffic data maps to determine how many people will drive by and perhaps where they are going.

You can especially use it to market. If you sell office services, wouldn’t it be great to know where all the OFFICES ARE?

Our map of dental practices not only told us WHO is out there, but what routes best maximized our sales routes. We learned who the office managers are, (roughly, they may come and go) who the gatekeepers are and who the dentists are!

In the end though, any information is only valuable if you have a use for it. As we have mentioned through our articles, we believe the best advice for a startup is to read as many of the pages on our site as you need to. Then, think through what may work best for you. Choose a few and start AB testing.